package com.ysd.dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.util.List;


import com.ysd.entity.ProductCatalogue;
import com.ysd.util.DBManager;

public class ProductCatalogueDaoImpl {
	//查询产品系列表
	 public List<ProductCatalogue> getPCList() {
		    List<ProductCatalogue> list=new ArrayList<ProductCatalogue>();
	    	Connection conn=DBManager.getConnection();//建立连接
	    	Statement st=null;
	    	ResultSet rs=null;
	    	ProductCatalogue productCatalogue=null;
	    	try {//正常
				st=conn.createStatement();//创建statement对象，这个对象的作用是执行sql语句
				String sql="select * from productCatalogue";//执行sql语句
				rs=st.executeQuery(sql);//使用executeQuery查询sql语句
				while(rs.next()) {//遍历结果集
					int productCatalogueId=rs.getInt("productCatalogueId");
					String productCatalogueName=rs.getString("productCatalogueName");
					String productCatalogueImg=rs.getString("productCatalogueImg");
					productCatalogue=new ProductCatalogue(productCatalogueId,productCatalogueName,productCatalogueImg);
					list.add(productCatalogue);
					
				}
				return list;//正常下返回list集合
			} catch (SQLException e) {//异常
				e.printStackTrace();
				return null;//异常下返回空
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
	    }
	 //分页查询
	 public List<ProductCatalogue> getproductCateloguePage(int page,int size) {
		    List<ProductCatalogue> list=new ArrayList<ProductCatalogue>();
	    	Connection conn=DBManager.getConnection();//建立连接
	    	Statement st=null;
	    	ResultSet rs=null;
	    	ProductCatalogue productCatalogue=null;
	    	try {//正常
				st=conn.createStatement();//创建statement对象，这个对象的作用是执行sql语句
				String sql="select * from productCatalogue LIMIT "+(page-1)*size+","+size+"";//执行sql语句
				rs=st.executeQuery(sql);//使用executeQuery查询sql语句
				System.out.print(sql);
				while(rs.next()) {//遍历结果集
					int productCatalogueId=rs.getInt("productCatalogueId");
					String productCatalogueName=rs.getString("productCatalogueName");
					String productCatalogueImg=rs.getString("productCatalogueImg");
					productCatalogue=new ProductCatalogue(productCatalogueId,productCatalogueName,productCatalogueImg);
					list.add(productCatalogue);
					
				}
				return list;//正常下返回list集合
			} catch (SQLException e) {//异常
				e.printStackTrace();
				return null;//异常下返回空
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
	    }
	 /**
	  * 根据产品分类id删除产品分类
	  * cateId 分类id
	      * 数据库受影响行数
	  */
	 public int delProductCatalogueId(int id) {
		 return DBManager.executeUpdate("DELETE FROM productcatalogue WHERE  productcatalogueId="+id);
	 }
	 
	 /***
	  * 添加
	  */
	 public int addProductCatalogue(ProductCatalogue pc) {
		 return DBManager.executeUpdate("insert into  productcatalogue values(null,'"+pc.getProductCatalogueName()+"',null)");
	 }
	 /***
	  * 查询系列名称
	  * @param name
	  * @return
	  */
	 public List<ProductCatalogue> getProductCatalogueByName(String name){
		 List<ProductCatalogue> pcList=new ArrayList<ProductCatalogue>();
		 Connection conn=DBManager.getConnection();
		 Statement st=null;
		 ResultSet rs=null;
		 try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM productcatalogue where productCatalogueName='"+name+"'");
			while(rs.next()) {
				int productCatalogueId=rs.getInt("productCatalogueId");
				String productCatalogueName=rs.getString("productCatalogueName");
				String productCatalogueImg=rs.getString("productCatalogueImg");
				ProductCatalogue p=new ProductCatalogue(productCatalogueId, productCatalogueName, productCatalogueImg);
				pcList.add(p);
			}
			return pcList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	 }
	 /**
	  * 根据id查询
	  */
	 public ProductCatalogue  getProductCatalogueById(int cateId) {
		 ProductCatalogue pc=null;
		 Connection conn=DBManager.getConnection();
		 Statement st=null;
		 ResultSet rs=null;
		 try {
			 st=conn.createStatement();
			 rs=st.executeQuery("SELECT * FROM productcatalogue WHERE productCatalogueId="+cateId);
			 while(rs.next()) {
				 int productCatalogueId=rs.getInt(1);
				 String productCatalogueName=rs.getString(2);
				 String productCatalogueImg=rs.getString(3);
				 pc=new ProductCatalogue(productCatalogueId, productCatalogueName, productCatalogueImg);
			 }
			 return pc;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	 }
	 //修改产品分类
	 public int updateProductCatalogueById(ProductCatalogue pc) {
		 return DBManager.executeUpdate("UPDATE productcatalogue SET productCatalogueName='"+pc.getProductCatalogueName()+"' WHERE productCatalogueId='"+pc.getProductCatalogueId()+"'");
	 }
	 //批量删除
	 public int delectProductCatalogueByIds(String ids) {
		 String sql="DELETE FROM productcatalogue WHERE productcatalogueId in("+ids+")";
		 System.out.println(sql);
		 return DBManager.executeUpdate(sql);
	}
}
